<?php
class sis_tagihan_anggota_model extends MY_Model {
	var $model = 'sis_tagihan_anggota';
	
	function GetJSON()
	{
		$ls_sql	= "	SELECT  TGH.ID_ANGGOTA, TO_CHAR(TGH.TGL_TAGIHAN,'DD/MM/YYYY') TGL_TAGIHAN, 
							TGH.SIMP_WAJIB, TGH.SIMP_POKOK, TGH.SIMP_SUKARELA, TGH.CICILAN_PINJAMAN, TGH.TOTAL_TAGIHAN, TGH.SISA_TAGIHAN, 
							TGH.ST_LUNAS, TO_CHAR(TGH.TGL_LUNAS,'DD/MM/YYYY') TGL_LUNAS,
							PRS.FULL_NAME, JNSAGT.NAMA_JENIS_ANGGOTA, COMP.NAMA_PERUSAHAAN
					FROM    SIS_TAGIHAN_ANGGOTA TGH
							JOIN SIS_ANGGOTA AGT ON TGH.ID_ANGGOTA = AGT.ID_ANGGOTA
							JOIN SW_PERSON PRS ON PRS.PERSON_ID = AGT.PERSON_ID
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA JNSAGT ON JNSAGT.KD_JENIS_ANGGOTA = AGT.KD_JENIS_ANGGOTA
							LEFT JOIN SW_PERUSAHAAN COMP ON AGT.ID_PERUSAHAAN = COMP.ID_PERUSAHAAN
					WHERE	AGT.DELETE_DATE IS NULL
							AND UPPER(NVL(PRS.FULL_NAME,'')) LIKE '%".strtoupper(post('full_name'))."%'
							
							".(post('id_perusahaan') != '' ? " AND COMP.id_perusahaan = '".post('id_perusahaan')."' " : '')."
							".(post('kode_perusahaan') != '' ? " AND COMP.kode_perusahaan = '".post('kode_perusahaan')."' " : '')."
							".(post('nama_perusahaan') != '' ? " AND COMP.nama_perusahaan = '".post('nama_perusahaan')."' " : '')."
							
							".(post('kd_jenis_anggota') != '' ? " AND AGT.KD_JENIS_ANGGOTA = '".post('kd_jenis_anggota')."' " : '')."
							".(post('id_anggota') != '' ? " AND AGT.ID_ANGGOTA = '".post('id_anggota')."' " : '')."
							".(post('tgl_awal') != '' ? " AND TGH.TGL_TAGIHAN >= TO_DATE('".post('tgl_awal')."','DD/MM/YYYY') " : '')."
							".(post('tgl_akhir') != '' ? " AND TGH.TGL_TAGIHAN <= TO_DATE('".post('tgl_akhir')."','DD/MM/YYYY') " : '')."
					ORDER BY ".(post('sort')=='' ? 'TGH.TGL_TAGIHAN ASC, AGT.ID_ANGGOTA' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
		
		return json_encode( $this->paging($ls_sql) );
	}
	
	function Add($arr_data){
		$this->db->insert('SIS_TAGIHAN_ANGGOTA', $arr_data);
	}
	
	function Update($arr_data, $arr_rule){
		$this->db->update('SIS_TAGIHAN_ANGGOTA', $arr_data, $arr_rule);
	}
	
	function GetSingle($as_idanggota){
		$result = $this->db->get_where('SIS_TAGIHAN_ANGGOTA', array('ID_ANGGOTA' => $as_idanggota));
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	function GetSingleView($as_idanggota)
	{
		$ls_sql	= "	SELECT	A.ID_ANGGOTA, A.ST_AKTIF, 
							(CASE NVL(A.ST_AKTIF,'0') WHEN '0' THEN 'Daftar' WHEN '1' THEN 'Aktif' ELSE 'Non Aktif' END) NAMA_STATUS,
							A.KD_BANK, F.NAMA_BANK, A.NAMA_CABANG, A.NO_REKENING, A.PEMILIK_REKENING, 
							B.PERSON_ID, B.ID_KARYAWAN_PK, B.GENDER_ID, B.ID_AGAMA_PK, B.ID_JENJANG_PK, B.KD_KANTOR, B.FULL_NAME, B.MOTHER_NAME,
							B.IDENTITY_TYPE_CODE, B.IDENTITY_NUMBER, B.NIP, B.BIRTH_PLACE, TO_CHAR(B.BIRTH_DATE,'DD/MM/YYYY') BIRTH_DATE, B.EMAIL, B.PHONE, B.USER_ID, 
							C.KD_JENIS_ANGGOTA, C.NAMA_JENIS_ANGGOTA, 
							D.ADDRESS_TYPE_CODE, D.ADDRESS, D.RT, D.RW, D.VILLAGE, D.SUBDISTRICT, 
							D.CITY_CODE, D.PROVINCE_CODE, D.COUNTRY_CODE, D.ZIPCODE, E.GENDER_NAME,
							G.ID_PERUSAHAAN, G.KODE_PERUSAHAAN, G.NAMA_PERUSAHAAN,
							(SELECT X.JUMLAH FROM SIS_TAGIHAN_ANGGOTA_VARIABEL X WHERE X.ID_ANGGOTA = A.ID_ANGGOTA AND X.ID_VARIABEL = 'POKOK') SIMPANAN_POKOK,
							(SELECT X.JUMLAH FROM SIS_TAGIHAN_ANGGOTA_VARIABEL X WHERE X.ID_ANGGOTA = A.ID_ANGGOTA AND X.ID_VARIABEL = 'WAJIB') SIMPANAN_WAJIB,
							(SELECT X.JUMLAH FROM SIS_TAGIHAN_ANGGOTA_VARIABEL X WHERE X.ID_ANGGOTA = A.ID_ANGGOTA AND X.ID_VARIABEL = 'SUKARELA') SIMPANAN_SUKARELA,
							(SELECT X.JUMLAH FROM SIS_TAGIHAN_ANGGOTA_VARIABEL X WHERE X.ID_ANGGOTA = A.ID_ANGGOTA AND X.ID_VARIABEL = 'TGL_IURAN') TGL_IURAN,
							(SELECT X.JUMLAH FROM SIS_TAGIHAN_ANGGOTA_VARIABEL X WHERE X.ID_ANGGOTA = A.ID_ANGGOTA AND X.ID_VARIABEL = 'PLAFOND') PLAFOND,
							(SELECT TO_CHAR(X.TGL_STATUS,'DD/MM/YYYY') TGL_STATUS FROM SIS_TAGIHAN_ANGGOTA_HISTORY X WHERE X.ID_ANGGOTA = A.ID_ANGGOTA AND X.KODE_STATUS_ANGGOTA = 'ST001') TGL_DAFTAR
					FROM	SIS_TAGIHAN_ANGGOTA A 
							LEFT JOIN SW_PERSON B ON A.PERSON_ID = B.PERSON_ID 
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA C ON A.KD_JENIS_ANGGOTA = C.KD_JENIS_ANGGOTA
							LEFT JOIN SW_PERSON_ADDRESS D ON B.PERSON_ID = D.PERSON_ID  AND D.ADDRESS_TYPE_CODE = 'ADR01' 
							LEFT JOIN SW_GENDER E ON B.GENDER_ID = E.GENDER_ID
							LEFT JOIN KEU_BANK F ON A.KD_BANK = F.KD_BANK
							LEFT JOIN SW_PERUSAHAAN G ON A.ID_PERUSAHAAN = G.ID_PERUSAHAAN
					WHERE	A.ID_ANGGOTA = '".$as_idanggota."'";

		$result = $this->db->query($ls_sql);
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}

	function DeleteSingle($arr_rule)
	{
		try {
			if(	intval($this->get_data("SELECT COUNT(1) FROM SIS_PINJAMAN_ANGGOTA WHERE ID_ANGGOTA = '".$arr_rule['id_anggota']."'"))>0 ||
				intval($this->get_data("SELECT COUNT(1) FROM SIS_TRANSAKSI_ANGGOTA WHERE ID_ANGGOTA = '".$arr_rule['id_anggota']."'"))>0 )
			{
				return $this->error_delete.' [id: '.$arr_rule['ID_ANGGOTA']."]<br />";
			}else{
				//$this->db->delete('SIS_TAGIHAN_ANGGOTA', array('ID_ANGGOTA' => $arr_rule['ID_ANGGOTA']));
				$this->db->update('SIS_TAGIHAN_ANGGOTA', array('DELETE_USER' => USERID, 'DELETE_DATE' => NOW), array('ID_ANGGOTA' => $arr_rule['id_anggota']));
				$this->db->update('SW_PERSON', array('DELETE_USER' => USERID, 'DELETE_DATE' => NOW), array('PERSON_ID' => $arr_rule['person_id']));
				return '';
			}
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}
}
?>